﻿Imports System.Data.OracleClient
Imports System.IO
Namespace persistent
    Public Class BSCSCampaignDefinition_DAO
        Dim ExceptionRaised As Boolean

        Public Function GetDataTableFromCommand(_Command As OracleCommand) As DataTable

            Dim TableDataSet As New DataSet()
            Dim TableDataAdapter As New OracleDataAdapter()

            ExceptionRaised = False

            Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            _Command.Connection = TableConnection
            Try
                TableConnection.Open()

                TableDataAdapter = New OracleDataAdapter(_Command)
                TableDataAdapter.Fill(TableDataSet, "Query")

            Catch ex As Exception

                ExceptionRaised = True
                Dim LengthofExMessage As Integer
                LengthofExMessage = ex.ToString.Length - 1
                If LengthofExMessage >= 3900 Then
                    LengthofExMessage = 3900
                End If

                'LogFailure(ex.ToString().Substring(0, LengthofExMessage))
            Finally
                TableConnection.Close()
            End Try
            If ExceptionRaised = False Then
                Return TableDataSet.Tables(0)
            Else
                Return Nothing
            End If

        End Function

        'Public Shared Function GetTariffList(ByVal keyword As String) As DataTable
        '    Dim query As String = "SELECT tmcode value, des Text FROM rateplan WHERE upper(tmcode||des) like upper('%" & keyword & "%')"
        '    Dim dtTariffs As DataTable = persistent.myDBConn.myORATable(query)
        '    Return dtTariffs
        'End Function

        Public Function GetTariffList(keyword As String) As DataTable
            Dim TariffListCommand As New OracleCommand

            TariffListCommand.CommandText = "EU_BILL.BSCS_CAMPAIGN_DEFINITION_API.GetTariffList"
            TariffListCommand.CommandType = CommandType.StoredProcedure
            TariffListCommand.Parameters.Add("pis_Keyword", OracleType.VarChar).Value = keyword
            TariffListCommand.Parameters.Add("poc_TariffList", OracleType.Cursor).Direction = ParameterDirection.Output

            Return GetDataTableFromCommand(TariffListCommand)
        End Function

        'Public Shared Function GetTariffType(ByVal tmcode As String) As Integer
        '    Dim query As String = "select nvl((SELECT DECODE(tmtype,'M',1,'I',1,2) value FROM tarifftype WHERE tmcode = '" & tmcode & "'),0) from dual"
        '    Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
        '    Return retVal
        'End Function

        Public Function GetTariffType(tmcode As String) As Integer
            Dim TariffTypeCommand As New OracleCommand

            TariffTypeCommand.CommandText = "EU_BILL.BSCS_CAMPAIGN_DEFINITION_API.GetTariffType"
            TariffTypeCommand.CommandType = CommandType.StoredProcedure
            TariffTypeCommand.Parameters.Add("pis_Tmcode", OracleType.VarChar).Value = tmcode
            TariffTypeCommand.Parameters.Add("vn_TariffType", OracleType.Number).Direction = ParameterDirection.ReturnValue

            Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            TariffTypeCommand.Connection = TableConnection
            Try
                TableConnection.Open()
                TariffTypeCommand.ExecuteNonQuery()

            Catch ex As Exception


            Finally
                TableConnection.Close()
            End Try

            Return TariffTypeCommand.Parameters("vn_TariffType").Value
        End Function

        'Public Shared Function GetTariffLineType(ByVal tmcode As String) As Integer
        '    Dim query As String = "select nvl((SELECT DECODE(upper(tariff_group),'SES',1,'FCT',2,'DATA',3,0) value FROM eu_bpm.tariff_groups WHERE tmcode = '" & tmcode & "'),0) from dual"
        '    Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
        '    Return retVal
        'End Function

        Public Function GetTariffLineType(tmcode As String) As Integer
            Dim TariffLineTypeCommand As New OracleCommand

            TariffLineTypeCommand.CommandText = "EU_BILL.BSCS_CAMPAIGN_DEFINITION_API.GetTariffLineType"
            TariffLineTypeCommand.CommandType = CommandType.StoredProcedure
            TariffLineTypeCommand.Parameters.Add("pis_Tmcode", OracleType.VarChar).Value = tmcode
            TariffLineTypeCommand.Parameters.Add("vn_TariffLineType", OracleType.Number).Direction = ParameterDirection.ReturnValue

            Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            TariffLineTypeCommand.Connection = TableConnection
            Try
                TableConnection.Open()
                TariffLineTypeCommand.ExecuteNonQuery()

            Catch ex As Exception


            Finally
                TableConnection.Close()
            End Try

            Return TariffLineTypeCommand.Parameters("vn_TariffLineType").Value
        End Function



        'Public Shared Function GetTariffSegment(ByVal tmcode As String, Optional ByVal type As String = "A") As Integer
        '    Dim query As String
        '    If type = "B" Then
        '        query = "select nvl((SELECT DECODE(upper(tmtype),'M',1,'I',2,0) value FROM tarifftype WHERE tmcode = '" & tmcode & "'),0) from dual"
        '    ElseIf type = "K" Then
        '        query = "select nvl((SELECT DECODE(upper(tmtype),'S',1,'C',2,0) value FROM tarifftype WHERE tmcode = '" & tmcode & "'),0) from dual"
        '    Else
        '        query = "select nvl((SELECT DECODE(upper(tmtype),'M',1,'I',2,'S',3,'C',4,0) value FROM tarifftype WHERE tmcode = '" & tmcode & "'),0) from dual"
        '    End If

        '    Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
        '    Return retVal
        'End Function

        Public Shared Function GetTariffSegment(ByVal tmcode As String, Optional ByVal type As String = "A") As Integer
            Dim TariffSegmentCommand As New OracleCommand

            TariffSegmentCommand.CommandText = "EU_BILL.BSCS_CAMPAIGN_DEFINITION_API.GetTariffSegment"
            TariffSegmentCommand.CommandType = CommandType.StoredProcedure
            TariffSegmentCommand.Parameters.Add("pis_Tmcode", OracleType.VarChar).Value = tmcode
            TariffSegmentCommand.Parameters.Add("pis_TariffType", OracleType.VarChar).Value = tmcode
            TariffSegmentCommand.Parameters.Add("vn_TariffType", OracleType.Number).Direction = ParameterDirection.ReturnValue

            Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            TariffSegmentCommand.Connection = TableConnection
            Try
                TableConnection.Open()
                TariffSegmentCommand.ExecuteNonQuery()

            Catch ex As Exception


            Finally
                TableConnection.Close()
            End Try

            Return TariffSegmentCommand.Parameters("vn_TariffType").Value
        End Function

        Public Shared Function GetTariffDeviceSegment(ByVal tmcode As String) As Integer
            Dim query As String = "select nvl((SELECT group_id value FROM EU_BILL.PRODUCT_GROUP_TARIFFS WHERE tmcode = '" & tmcode & "'),0) from dual"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetGPOSegment(ByVal tmcode As String) As String
            Dim query As String = "select nvl((select distinct tag value FROM eu_bill.gpo_tariff_tag_config WHERE tmcode = '" & tmcode & "'),'NA' ) value from dual"
            Dim retVal As String = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetGPOList() As DataTable
            Dim query As String = "select distinct tag value,tag text FROM eu_bill.gpo_tariff_tag_config"
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

        Public Shared Function GetCorpSMS(ByVal tmcode As String) As Integer
            Dim query As String = "SELECT decode(count(*),0,2,1) FROM EU_BILL.CORPSMS_TARIFFS WHERE tmcode = '" & tmcode & "'"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetPOSTariff(ByVal tmcode As String) As Integer
            Dim query As String = "SELECT decode(count(*),0,2,1) FROM EU_BILL.WA_GLOBAL_CONFIGURATION WHERE WA_NAME='TAX_INTERFACE' and PARAMETER_NAME='POS_TARIFFS' AND PARAMETER_VALUE like '%:" & tmcode & ":%'"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetJETTariff(ByVal tmcode As String) As Integer
            Dim query As String = "SELECT decode(count(*),0,2,1) FROM EU_BILL.TARIFF_GROUP_DETAILS WHERE GROUP_ID = 1 AND tmcode =  '" & tmcode & "'"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetM2MTariff(ByVal tmcode As String) As Integer
            Dim query As String = "SELECT decode(count(*),0,2,1) FROM EU_BILL.WA_GLOBAL_CONFIGURATION WHERE WA_NAME='TAX_INTERFACE' and PARAMETER_NAME='M2M_TARIFFS' AND PARAMETER_VALUE like '%:" & tmcode & ":%'"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetDeviceFee() As DataTable
            Dim query As String = "SELECT (tariff_definition_id || '_' || service_definition_id) value, fee_description || ' - ' || fee_amount || ' TL' text  FROM eu_bill.tsf_scenario WHERE enabled = 'Y'"
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

        Public Shared Function GetPriceGroupNodes(ByVal lb As ListBox) As DataTable

            Dim notInId As String = "'0'"
            For Each li As ListItem In lb.Items
                notInId = notInId & ",'" & li.Value & "'"
            Next
            If notInId <> "" Then
                notInId = "(" & notInId & ")"
            End If
            Dim query As String = "SELECT * FROM ( SELECT * FROM ( SELECT id value,description text,parent_id, depth FROM eu_bill.price_group_reasons) t WHERE (t.depth = 0 AND 0 < (SELECT count(*) FROM eu_bill.price_group_reasons where nvl(parent_id,0) = t.value AND id not IN " & notInId & ")) or t.depth > 0) where value not in " & notInId
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

        Public Shared Function GetProrationInfo(ByVal tmcode As String) As Integer
            Dim query As String = "select NVL((SELECT decode(proind,'Y',1,0) value  FROM mpulktmb m1 WHERE tmcode = '" & tmcode & "' AND sncode IN (77,99) AND m1.vscode = (SELECT max(m2.vscode) FROM mpulktmb m2 WHERE m2.tmcode = m1.tmcode)),0) FROM dual"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetSegmentType(ByVal tmcode As String) As Integer
            Dim query As String = "select nvl((SELECT DECODE(dwh_segment,'MASS',1,'INST',2,'LE',3,'SME',4,'CORP',5) value FROM EU_BILL.CUSTOMER_SEGM_TM_CONFIG WHERE tmcode = '" & tmcode & "'),0) from dual"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetContentType(ByVal tmcode As String) As Integer
            Dim query As String = "select nvl((SELECT DECODE(dwh_segment,'MASS',1,'INST',2,'LE',3,'SME',4,'CORP',5) value FROM EU_BILL.CUSTOMER_SEGM_TM_CONFIG WHERE tmcode = '" & tmcode & "'),0) from dual"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetKytDetail(ByVal tmcode As String) As Integer
            Dim query As String = "select nvl((SELECT DECODE(content_type,'SES + SMS',1,'DATA + SES',2,'SES',3,'DATA + SES + SMS',4,'DATA + SMS',5,'SMS',6,'DATA',7) value FROM eu_bill.Tariff_content_type WHERE tmcode = '" & tmcode & "'),0) from dual"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetDwhMnoType() As DataTable
            Dim query As String = "SELECT distinct mno_type value, mno_type text FROM EU_BILL.MNO_TYPE_TMCODE"
            Dim dtTariffs As DataTable = persistent.myDBConn.myORATable(query)
            Return dtTariffs
        End Function

        Public Shared Function GetPriceGroupList() As DataTable
            Dim query As String = "SELECT distinct prgcode value, prgname text FROM pricegroup_all"
            Dim dtTariffs As DataTable = persistent.myDBConn.myORATable(query)
            Return dtTariffs
        End Function

        Public Shared Function GetSMSItemsToBeWorked() As DataTable
            Dim query As String = "SELECT temp_tariff_id value, temp_tariff_id text  FROM eu_bill.tariff_price_group GROUP BY temp_tariff_id"
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

        Public Shared Function GetPriceGroupCountForTariff(ByVal tmcode As String) As Integer
            Dim query As String = "select count(*) value, count(*) text FROM  eu_bill.tariff_price_group WHERE temp_tariff_id = '" & tmcode & "'"
            Dim retVal As Integer = persistent.myDBConn.ORAScalar(query)
            Return retVal
        End Function

        Public Shared Function GetPriceGroupReasonsOfTariff(ByVal tmcode As String, ByVal pg As String) As DataTable
            Dim query As String = "select distinct b.id value,b.description text, b.parent_id root from eu_bill.tariff_price_group_detail a, eu_bill.price_group_reasons b " & _
                                  " where a.temp_tariff_id = '" & tmcode & "' and a.price_group_id ='" & pg & "'" & _
                                  "   and (a.reason_id = b.id or b.id<0)"
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

        Public Shared Function GetPriceGroupFeeDetailsOfTariff(ByVal tmcode As String) As DataTable
            Dim query As String = "SELECT price_group_id value,mf text,decode(commitment_mf,null,-1,commitment_mf) cmf FROM eu_bill.tariff_price_group a where a.temp_tariff_id = '" & tmcode & "'"
            Dim retVal As DataTable = persistent.myDBConn.myORATable(query)
            Return retVal
        End Function

    End Class
End Namespace




